{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "785f5e07-1a45-44c8-bbd7-17b193f804b3",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pymysql\n",
    "import pandas as pd\n",
    "import pymysql.cursors\n",
    "from sklearn.preprocessing import MinMaxScaler\n",
    "\n",
    "\n",
    "class MysqlUtils(object):\n",
    "    def __init__(self):\n",
    "        self.conn = pymysql.connect(\n",
    "            host='127.0.0.1',\n",
    "            user='root',\n",
    "            passwd='root',\n",
    "            db='scenic',\n",
    "            port=3306,\n",
    "            charset='utf8'\n",
    "        )\n",
    "\n",
    "    def is_holiday(self, data):\n",
    "        \"\"\"是否节假日\"\"\"\n",
    "        if data in ['2024-09-03', '2024-10-01', '2024-10-02', '2024-10-03', '2024-10-04', '2024-10-05']:\n",
    "            return 1\n",
    "        return 0\n",
    "\n",
    "    def get_scenic_data(self):\n",
    "        \"\"\"获取数据\"\"\"\n",
    "        cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)\n",
    "        sql = \"\"\"\n",
    "        SELECT DATE(g.create_time) as date, HOUR(g.create_time) as hour, count(*) as count\n",
    "        FROM order_user_gate_rel g\n",
    "        WHERE HOUR(g.create_time) BETWEEN 6 and 23 GROUP BY date, hour\n",
    "        \"\"\"\n",
    "        cursor.execute(sql)\n",
    "        df = pd.DataFrame(cursor.fetchall())\n",
    "        all_dates = pd.date_range(start=df['date'].min(), end=df['date'].max())\n",
    "        all_hours = list(range(6, 24))\n",
    "        full_index = pd.MultiIndex.from_product([all_dates, all_hours], names=['date', 'hour'])\n",
    "        df_full = df.set_index(['date', 'hour']).reindex(full_index, fill_value=0).reset_index()\n",
    "\n",
    "        # 按天组织数据（每行包含18个小时的检票次数）\n",
    "        df_pivot = df_full.pivot(index='date', columns='hour', values='count')\n",
    "        # print(df_pivot.head)\n",
    "        df_pivot['dow'] = df_pivot.index.dayofweek  # 星期几（0-6）\n",
    "        df_pivot['month'] = df_pivot.index.month  # 月份\n",
    "        df_pivot['is_holiday'] = df_pivot.index.map(self.is_holiday)\n",
    "\n",
    "        # 对星期几和月份进行独热编码\n",
    "        df_pivot = pd.get_dummies(df_pivot, columns=['dow','month'], dtype=int)\n",
    "        # print(df_pivot.head)\n",
    "        # 对小时列进行归一化\n",
    "        hours_columns = list(range(6, 24))\n",
    "        df_hours = df_pivot[hours_columns].copy()\n",
    "\n",
    "        feature_columns = [col for col in df_pivot.columns if col not in hours_columns]\n",
    "        df_feature = df_pivot[feature_columns].copy()\n",
    "\n",
    "        scaler = MinMaxScaler()\n",
    "        scaled_hours = scaler.fit_transform(df_hours)\n",
    "\n",
    "        # 将归一化后的数据转化为DataFrame\n",
    "        df_hours_scaled = pd.DataFrame(scaled_hours, columns=hours_columns, index=df_hours.index)\n",
    "\n",
    "        # 合并\n",
    "        df_pivot_clean = pd.concat([df_hours_scaled, df_feature], axis=1)\n",
    "        print(df_pivot_clean.head)\n",
    "        df_pivot_clean.to_csv('NN/scenic_data.csv', index=False)\n",
    "\n",
    "\n",
    "if __name__ == '__main__':\n",
    "    mu = MysqlUtils()\n",
    "    mu.get_scenic_data()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
